package dao;

import java.sql.SQLException;


import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.jdbc.core.JdbcTemplate;


import entity.City;
import entity.Country;
import entity.Hotel;
import entity.Hotel_label;
import entity.Hotel_reserve;
import entity.Payment;
import entity.ZC_user;

public class HotelDao {
	private QueryRunner runner = new QueryRunner(DBPool.getInstance().getDataSource());
	private JdbcTemplate template = new JdbcTemplate(DBPool.getInstance().getDataSource());
	/**
	 * 查询所有，国家 
	 * @throws SQLException 
	 */
	public List<Country> findCoutry() throws SQLException{
		// 1、sql语句
		String sql = "select * from country";
		return runner.query(sql, new BeanListHandler<Country>(Country.class));
	}
	public List<City> findCity(int country_id) throws SQLException {
		String sql = "select * from city where country_id =?";
		Object[] params = {country_id};	
		return runner.query(sql, new BeanListHandler<City>(City.class), params);
	}
	public Country findCountryName(int country_id) throws SQLException {
		String sql ="select * from country where country_id =?";
		Object[] params = {country_id};	
		return runner.query(sql, new BeanHandler<Country>(Country.class), params);
	}
	public List<City> findCityIDByCountryId(int country_id) throws SQLException {
		String sql ="select city_id from city where country_id =?";
		Object[] params = {country_id};	
		return runner.query(sql, new BeanListHandler<City>(City.class), params);
	}
	public List<Hotel> findAllStore() throws SQLException {
		String sql ="select * from hotel";
		return runner.query(sql, new BeanListHandler<Hotel>(Hotel.class));
	}
	
	public List<Hotel_label> findHotelLabel(int hotel_id) throws SQLException{
		String sql = "select * from hotel_label where hotel_id=?";
		Object[] params = {hotel_id};
		return runner.query(sql, new BeanListHandler<Hotel_label>(Hotel_label.class),params);
	}
	
	public int findTotalCount(int city_id){
		String sql = "select count(*) from hotel where city_id=?";
		return template.queryForObject(sql, Integer.class,city_id);
	}
	public List<Hotel> findByPage(int city_id, int start, int pageSize){
		List<Hotel> lis = null;
		String sql = "select * from hotel where city_id = ? limit ?,?";
		Object[] params = {city_id,start,pageSize};
		try {
			lis = runner.query(sql, new BeanListHandler<Hotel>(Hotel.class), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lis;
	}
	public List<Hotel> findStoreByCityId(int city_id) throws SQLException {
		String sql ="select * from hotel where city_id =?";
		Object[] params= {city_id};
		return runner.query(sql, new BeanListHandler<Hotel>(Hotel.class),params);
	}
	public int findAllTotalCount() {
		String sql = "select count(*) from hotel";
		return template.queryForObject(sql, Integer.class);
	}
	public List<Hotel> findByAllPages(int start, int pageSize) {
		List<Hotel> lis = null;
		String sql = "select * from hotel limit ?,?";
		Object[] params = {start,pageSize};
		try {
			lis = runner.query(sql, new BeanListHandler<Hotel>(Hotel.class), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lis;
	}
	
	public List<Hotel> getFreshProduct(int city_id) {
		List<Hotel> hotel = null;
		String sql ="select * from hotel where city_id=?";
		Object[] params = {city_id};
		try {
			hotel = runner.query(sql, new BeanListHandler<Hotel>(Hotel.class),params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return hotel;
	}
	
	public City getCityIdByCityName(String city_name) throws SQLException {
		String sql ="select city_id from city where city_name=?";
		Object[] params= {city_name};
		return runner.query(sql, new BeanHandler<City>(City.class), params);
		
		// return template.queryForObject(sql, Integer.class,city_name);
	}
	public Hotel findHotelByHotelId(int hotel_id){
		String sql = "select * from hotel where hotel_id=?";
		Object[] params = {hotel_id};
		try {
			return runner.query(sql, new BeanHandler<Hotel>(Hotel.class), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public Hotel_reserve findTypeByHotelId(int hotel_id) throws SQLException {
		String sql = "select * from hotel_reserve where hotel_id=?";
		Object[] params = {hotel_id};
		return runner.query(sql, new BeanHandler<Hotel_reserve>(Hotel_reserve.class), params);
	}
	public int addSeeNumber(int see_number,int hotel_id) {
		int num = 0;
		String sql = "update hotel_reserve set see_number =? where hotel_id = ?";
		// 浏览量+1
		see_number++;
		Object[] params = {see_number,hotel_id};
		try {
			num = runner.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	public Hotel_reserve querySeeNumber(int hotel_id) {
		Hotel_reserve h_reserve = null;
		String sql = "select see_number from hotel_reserve where reserve_id = ?";
		Object[] params = {hotel_id};
		try {
			h_reserve = runner.query(sql, new BeanHandler<Hotel_reserve>(Hotel_reserve.class), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return h_reserve;
	}
	public int querOrders(String number) {
		String sql = "update payment set status = 'Y' where number = ?";
		try {
			return runner.update(sql, number);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	public ZC_user queryZcUsername(int user_id) {
		String sql = "select * from zc_user where id = ?";
		try {
			return runner.query(sql, new BeanHandler<ZC_user>(ZC_user.class), user_id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public ZC_user queryZcUserid(String user_Name) {
		String sql = "select * from zc_user where userName = ?";
		try {
			return runner.query(sql, new BeanHandler<ZC_user>(ZC_user.class), user_Name);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public Payment queryPay(String number) {
		String sql = "select * from payment where number="+number;
		try {
			return runner.query(sql, new BeanHandler<Payment>(Payment.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public int updateHotel(int hotel_id,int sale_number,int save_number) {
		String sql = "update hotel set hotel_sale_number = ? ,hotel_save_number = ? where hotel_id = ?";
		Object[] params = {sale_number,save_number,hotel_id};
		try {
			return runner.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	public Hotel querHotel(int hotel_id) {
		System.out.println("dao............."+hotel_id);
		String sql = "select * from hotel where hotel_id = ?";
		try {
			return runner.query(sql, new BeanHandler<Hotel>(Hotel.class), hotel_id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}
